import Database from 'better-sqlite3'
import path from 'node:path'
import { app } from 'electron'

let db

export const initData = () => {
    const dbPath = path.join(app.getPath('userData'), 'data.db')
    db = new Database(dbPath)

    // dataBase 表
    db.prepare(`
        CREATE TABLE IF NOT EXISTS dataBase (
                                                id INTEGER PRIMARY KEY AUTOINCREMENT,
                                                type TEXT NOT NULL,
                                                status INTEGER DEFAULT 0,
                                                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                                                update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                                                username TEXT,
                                                password TEXT,
                                                port INTEGER,
                                                host TEXT,
                                                remark TEXT
        )
    `).run()

    db.prepare(`
    CREATE TRIGGER IF NOT EXISTS update_database_time
    AFTER UPDATE ON dataBase
    FOR EACH ROW
    BEGIN
      UPDATE dataBase SET update_time = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;
  `).run()

    // users 表
    db.prepare(`
        CREATE TABLE IF NOT EXISTS users (
                                             id INTEGER PRIMARY KEY AUTOINCREMENT,
                                             name TEXT NOT NULL,
                                             nickname TEXT,
                                             phone TEXT,
                                             email TEXT,
                                             sex TEXT,
                                             school TEXT,
                                             address TEXT,
                                             status INTEGER DEFAULT 0,       -- 0正常 1禁用
                                             is_delete INTEGER DEFAULT 0,    -- 0未删除 1已删除
                                             create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                                             update_time DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `).run()

    db.prepare(`
    CREATE TRIGGER IF NOT EXISTS update_users_time
    AFTER UPDATE ON users
    FOR EACH ROW
    BEGIN
      UPDATE users SET update_time = CURRENT_TIMESTAMP WHERE id = OLD.id;
    END;
  `).run()

    console.log('表建好了 ✅')
    return db
}
